﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.TravelLogs
{
    internal static class TravelLogDAL
    {
        internal static TravelLogEntity GetTravelLogByReader(IDataReader dr)
        {
            TravelLogEntity travellog = new TravelLogEntity();

            travellog.TravelLogID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            travellog.ObjectID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            travellog.ObjectType = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
            travellog.Title = dr.IsDBNull(3) ? null : dr.GetString(3);
            travellog.Content = dr.IsDBNull(4) ? null : dr.GetString(4);
            travellog.Editor = dr.IsDBNull(5) ? null : dr.GetString(5);
            travellog.Hits = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            travellog.Created = Util.ConverDateTime(dr.GetValue(7));
            travellog.Updated = Util.ConverDateTime(dr.GetValue(8));
            travellog.Sort = dr.IsDBNull(9) ? 0 : dr.GetInt32(9);
            travellog.IsEnable = dr.IsDBNull(10) ? false : dr.GetBoolean(10);

            return travellog;
        }

        internal static int GetTravelLogCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectTravelLog = @"SELECT COUNT([TravelLogID]) FROM [TravelLog]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectTravelLog, ref parameterlst);
            DbCommand SelectTravelLogCommand = db.GetSqlStringCommand(SelectTravelLog);
            DbHelper.PackDbCommandByDbParameters(SelectTravelLogCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectTravelLogCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<TravelLogEntity> GetTravelLogList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<TravelLogEntity> travelloglst = new List<TravelLogEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectTravelLog = @"
                    SELECT [TravelLogID],[ObjectID],[ObjectType],[Title],[Content],[Editor],[Hits],[Created],[Updated],[Sort],[IsEnable]
                    FROM (
                        SELECT [TravelLogID],[ObjectID],[ObjectType],[Title],[Content],[Editor],[Hits],[Created],[Updated],[Sort],[IsEnable], 
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectTravelLog += " " + sortField + " " + sortMode + ",";
            SelectTravelLog += @" [TravelLog].[TravelLogID] Desc
                    ) AS rowNum 
                        FROM [TravelLog] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectTravelLog, ref parameterlst);
            SelectTravelLog += @"
                    ) AS [TravelLog] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectTravelLogCommand = db.GetSqlStringCommand(SelectTravelLog);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectTravelLogCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectTravelLogCommand))
            {
                while (dr.Read())
                {
                    travelloglst.Add(GetTravelLogByReader(dr));
                }
            }

            return travelloglst;
        }

        internal static List<TravelLogEntity> GetTravelLogList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<TravelLogEntity> travelloglst = new List<TravelLogEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectTravelLog = @"
                    SELECT [TravelLogID],[ObjectID],[ObjectType],[Title],[Content],[Editor],[Hits],[Created],[Updated],[Sort],[IsEnable]
                    FROM [TravelLog] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectTravelLog, ref parameterlst);
            SelectTravelLog += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectTravelLog += " " + sortField + " " + sortMode + ",";
            SelectTravelLog += " [TravelLog].[TravelLogID] Desc ";

            DbCommand SelectTravelLogCommand = db.GetSqlStringCommand(SelectTravelLog);
            DbHelper.PackDbCommandByDbParameters(SelectTravelLogCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectTravelLogCommand))
            {
                while (dr.Read())
                {
                    travelloglst.Add(GetTravelLogByReader(dr));
                }
            }

            return travelloglst;
        }

        internal static int GetShopTravelLogCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectTravelLog = @"SELECT COUNT([TravelLog].[TravelLogID]) FROM [TravelLog] LEFT OUTER JOIN Shop ON TravelLog.ObjectID = Shop.ShopID AND TravelLog.ObjectType=1 ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectTravelLog, ref parameterlst);
            DbCommand SelectTravelLogCommand = db.GetSqlStringCommand(SelectTravelLog);
            DbHelper.PackDbCommandByDbParameters(SelectTravelLogCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectTravelLogCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<TravelLogEntity> GetShopTravelLogList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<TravelLogEntity> travelloglst = new List<TravelLogEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectTravelLog = @"
                    SELECT [TravelLogID],[ObjectID],[ObjectType],[Title],[Content],[Editor],[Hits],[Created],[Updated],[Sort],[IsEnable]
                    FROM (
                        SELECT [TravelLog].[TravelLogID],[TravelLog].[ObjectID],[TravelLog].[ObjectType],[TravelLog].[Title],[TravelLog].[Content],
                            [TravelLog].[Editor],[TravelLog].[Hits],[TravelLog].[Created],[TravelLog].[Updated],[TravelLog].[Sort],[TravelLog].[IsEnable], 
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectTravelLog += " " + sortField + " " + sortMode + ",";
            SelectTravelLog += @" [TravelLog].[TravelLogID] Desc
                    ) AS rowNum 
                        FROM [TravelLog] LEFT OUTER JOIN Shop ON TravelLog.ObjectID = Shop.ShopID AND TravelLog.ObjectType=1 ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectTravelLog, ref parameterlst);
            SelectTravelLog += @"
                    ) AS [TravelLog] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectTravelLogCommand = db.GetSqlStringCommand(SelectTravelLog);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectTravelLogCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectTravelLogCommand))
            {
                while (dr.Read())
                {
                    travelloglst.Add(GetTravelLogByReader(dr));
                }
            }

            return travelloglst;
        }

        internal static List<TravelLogEntity> GetShopTravelLogList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<TravelLogEntity> travelloglst = new List<TravelLogEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectTravelLog = @"
                    SELECT [TravelLog].[TravelLogID],[TravelLog].[ObjectID],[TravelLog].[ObjectType],[TravelLog].[Title],[TravelLog].[Content],
                        [TravelLog].[Editor],[TravelLog].[Hits],[TravelLog].[Created],[TravelLog].[Updated],[TravelLog].[Sort],[TravelLog].[IsEnable]
                    FROM [TravelLog] LEFT OUTER JOIN Shop ON TravelLog.ObjectID = Shop.ShopID AND TravelLog.ObjectType=1 ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectTravelLog, ref parameterlst);
            SelectTravelLog += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectTravelLog += " " + sortField + " " + sortMode + ",";
            SelectTravelLog += " [TravelLog].[TravelLogID] Desc ";

            DbCommand SelectTravelLogCommand = db.GetSqlStringCommand(SelectTravelLog);
            DbHelper.PackDbCommandByDbParameters(SelectTravelLogCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectTravelLogCommand))
            {
                while (dr.Read())
                {
                    travelloglst.Add(GetTravelLogByReader(dr));
                }
            }

            return travelloglst;
        }

        internal static TravelLogEntity GetTravelLog(int travellogID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            TravelLogEntity travellog = null;

            string SelectTravelLog = @"
                    SELECT [TravelLogID],[ObjectID],[ObjectType],[Title],[Content],[Editor],[Hits],[Created],[Updated],[Sort],[IsEnable]
                    FROM [TravelLog] 
                    WHERE [TravelLog].[TravelLogID]=@TravelLogID
                ";

            DbCommand SelectTravelLogCommand = db.GetSqlStringCommand(SelectTravelLog);
            db.AddInParameter(SelectTravelLogCommand, "@TravelLogID", DbType.Int32, travellogID);

            using (IDataReader dr = db.ExecuteReader(SelectTravelLogCommand))
            {
                if (dr.Read())
                {
                    travellog = GetTravelLogByReader(dr);
                }
            }

            return travellog;
        }

        internal static int InsertTravelLog(TravelLogEntity travellog)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder InsertTravelLog = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            InsertTravelLog.Append(@"Declare @TravelLogID int;
                    INSERT INTO [TravelLog] 
				([ObjectID],[ObjectType],[Title],[Content],[Editor],[Hits],[Created],[Updated],[Sort],[IsEnable])
				 VALUES (@ObjectID,@ObjectType,@Title,@Content,@Editor,@Hits,@Created,@Updated,@Sort,@IsEnable);
                    SELECT @TravelLogID=@@IDENTITY;
                ");

            parameterlst.Add(DbHelper.PackInParameter(db, "@ObjectID", DbType.Int32, travellog.ObjectID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ObjectType", DbType.Int32, travellog.ObjectType));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Title", DbType.String, travellog.Title));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Content", DbType.String, travellog.Content));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Editor", DbType.String, travellog.Editor));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Hits", DbType.Int32, travellog.Hits));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, travellog.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Updated", DbType.DateTime, travellog.Updated));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Sort", DbType.Int32, travellog.Sort));
            parameterlst.Add(DbHelper.PackInParameter(db, "@IsEnable", DbType.Boolean, travellog.IsEnable));


            InsertTravelLog.Append("SELECT @TravelLogID;");

            DbCommand InsertTravelLogCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertTravelLog", InsertTravelLog.ToString()));
            DbHelper.PackDbCommandByDbParameters(InsertTravelLogCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(InsertTravelLogCommand);

            return obj == null ? 0 : (int)obj;
        }

        internal static int UpdateTravelLog(TravelLogEntity travellog)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder UpdateTravelLog = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            UpdateTravelLog.Append(@"UPDATE [TravelLog] 
				 SET [ObjectID]=@ObjectID,[ObjectType]=@ObjectType,[Title]=@Title,[Content]=@Content,[Editor]=@Editor,[Hits]=@Hits,[Created]=@Created,[Updated]=@Updated,[Sort]=@Sort,[IsEnable]=@IsEnable 
				 WHERE [TravelLogID] = @TravelLogID;");

            parameterlst.Add(DbHelper.PackInParameter(db, "@TravelLogID", DbType.Int32, travellog.TravelLogID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ObjectID", DbType.Int32, travellog.ObjectID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ObjectType", DbType.Int32, travellog.ObjectType));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Title", DbType.String, travellog.Title));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Content", DbType.String, travellog.Content));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Editor", DbType.String, travellog.Editor));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Hits", DbType.Int32, travellog.Hits));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, travellog.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Updated", DbType.DateTime, travellog.Updated));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Sort", DbType.Int32, travellog.Sort));
            parameterlst.Add(DbHelper.PackInParameter(db, "@IsEnable", DbType.Boolean, travellog.IsEnable));


            DbCommand UpdateTravelLogCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_UpdateTravelLog", UpdateTravelLog.ToString()));
            DbHelper.PackDbCommandByDbParameters(UpdateTravelLogCommand, parameterlst.ToArray());

            result = db.ExecuteNonQuery(UpdateTravelLogCommand);

            return result;
        }

        internal static int DeleteTravelLog(int travellogID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteTravelLog = @"
                DELETE FROM [TravelLog] WHERE TravelLogID=@TravelLogID";

            DbCommand DeleteTravelLogCommand = db.GetSqlStringCommand(DeleteTravelLog);
            db.AddInParameter(DeleteTravelLogCommand, "@TravelLogID", DbType.Int32, travellogID);
            result = db.ExecuteNonQuery(DeleteTravelLogCommand);

            return result;
        }

        internal static int SetTravelLogEnable(int travellogID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetTravelLog = @"UPDATE [TravelLog]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [TravelLogID] = @TravelLogID";

            DbCommand SetTravelLogCommand = db.GetSqlStringCommand(SetTravelLog);
            db.AddInParameter(SetTravelLogCommand, "@TravelLogID", DbType.Int32, travellogID);
            result = db.ExecuteNonQuery(SetTravelLogCommand);

            return result;
        }

        internal static int HitTravelLog(int travellogID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetTravelLog = @"UPDATE [TravelLog]
                    SET [Hits] = [Hits]+1
                    WHERE [TravelLogID] = @TravelLogID";

            DbCommand SetTravelLogCommand = db.GetSqlStringCommand(SetTravelLog);
            db.AddInParameter(SetTravelLogCommand, "@TravelLogID", DbType.Int32, travellogID);
            result = db.ExecuteNonQuery(SetTravelLogCommand);

            return result;
        }
    }
}
